﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;

using Spire.Xls;
namespace JNCY.WinAutoComplate
{
    public class ExcelHelper
    {
        public string ExcelDir { get; set; }
        public string CurFilePath { get; set; }
        public string WatchPath { get; set; }

        public DataTable ReadExcelData()
        {
            CurFilePath = string.Empty;

            DataTable _dataresult = null;

            Workbook wb = new Workbook();

            string filepath = GetExcelPath(ExcelDir);

            if (!string.IsNullOrEmpty(filepath))
            {               
                using (FileStream _file = new FileStream(filepath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
                {
                    wb.LoadFromStream(_file);
                   // _file.Flush();
                }

                Worksheet wh = wb.Worksheets[0];

                CellRange range = wh.Range[wh.FirstRow, wh.FirstColumn, wh.LastRow, wh.LastColumn];

                //输出数据, 同时输出列名以及公式值
                _dataresult = wh.ExportDataTable(range, true, false);
            }

            return _dataresult;
        }

        private string GetExcelPath(string dir)
        {

            string pathdir = Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location) + Path.DirectorySeparatorChar + "";

            string path = string.Empty;
            if (!string.IsNullOrEmpty(dir))
            {
                path = Path.Combine(pathdir, dir);
            }
            else
            {
                path = Path.Combine(pathdir, "excel");
            }
       
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }

            WatchPath = path;
           // DirectoryInfo root = new DirectoryInfo(path);

            string[] files = Directory.GetFiles(path,"*",SearchOption.TopDirectoryOnly).Where(p=>p.EndsWith("xls")||p.EndsWith("xlsx")).ToArray();

            string fi = files.OrderByDescending(p => p.ToString()).FirstOrDefault();

            if (!string.IsNullOrEmpty(fi))
            {
                CurFilePath = fi;
                return fi;
            }
            return string.Empty;

        }
    }
}
